import openpyxl,os
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill
import tkinter

#在一个sheet页中，读取数据并按费用类型汇总费用
def readSheetData(workSheet, keyColName, valueColName, nameRowIdx, startRowIdx):
    #1.通过标题头转换成列编号
    #1.1标题行的数据，默认取数据的上一行，通过标题名称对比，获得列索引
    nameRowData = workSheet[nameRowIdx]
    for cell in nameRowData:
        #print(cell.column)
        if cell.value == keyColName:
            keyCol = cell.col_idx
        elif cell.value == valueColName:
            valueCol = cell.col_idx
    #2.汇总数据
    sheetDate = {}
    for row in range(startRowIdx, workSheet.max_row + 1):
        key = workSheet.cell(column=keyCol, row=row).value
        if key is not None:
            value = workSheet.cell(column=valueCol, row=row).value
            sheetDate.setdefault(key, 0)
            sheetDate[key] += value
    return sheetDate

#比较
def compareData(sheetDate1, sheetDate2):
    result = {}
    #先比较sheetDate2的key
    for key2 in sheetDate2.keys():
        value2 = sheetDate2.get(key2, 0)
        value1 = sheetDate1.get(key2, 0)
        result[key2] = value2 - value1
    #再比较sheetDate2无，但sheetDate1有的key
    for key1 in sheetDate1.keys():
        if key1 not in sheetDate2.keys():
            value1 = sheetDate1.get(key1, 0)
            result[key1] = -value1
    return result

#创建新的sheet，写入原数据和差异
def writeSheetData(workbook, sheetName, fileName1, sheetData1, fileName2, sheetData2, diffData):
    sheet = workbook.create_sheet(sheetName)
    #标题行
    row = 1
    sheet['A' + str(row)].value = "服务目录"
    sheet['B' + str(row)].value = fileName1
    sheet['C' + str(row)].value = fileName2
    sheet['D' + str(row)].value = "差额"
    # 数据行
    for key in diffData.keys():
        row = row + 1
        sheet['A' + str(row)].value = key
        if key in sheetData1.keys():
            sheet['B' + str(row)].value = sheetData1[key]
        else:
            sheet['B' + str(row)].value = 0
        sheet['C' + str(row)].value = diffData[key]
        sheet['D' + str(row)].value = diffData[key]
    # 汇总行
    row+=1
    sheet['A' + str(row)].value = "合计"
    sheet['B' + str(row)].value = "=SUM(B2:B"+str(row-1)+")"
    sheet['C' + str(row)].value = "=SUM(C2:C"+str(row-1)+")"
    sheet['D' + str(row)].value = "=C"+str(row)+"-B"+str(row)
    # 统一格式化单元格
    sheet.column_dimensions['A'].width = 40
    sheet.column_dimensions['B'].width = 40
    sheet.column_dimensions['C'].width = 40
    sheet.column_dimensions['D'].width = 40
    max_column_letter = openpyxl.utils.get_column_letter(sheet.max_column)
    for sheetRow in sheet['A1':max_column_letter+str(sheet.max_row)]:
        for cell in sheetRow:
            cellFormat(cell)


# 格式化单元格
def cellFormat(cell):
    cell.alignment = Alignment(horizontal='center', vertical='center')
    cell.border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'),
                    top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000'))


if __name__=='__main__':
    # 1.读取账单数据
    #file1 = '/Users/zhangxiangbin/GitRepos/fmcs-doc/100服贸农垦/SLA/2021年/招垦资本管理（北京）有限公司_I016-2021-10-2021-12_2021年10-12月计提账单.xlsx'
    #file2 = '/Users/zhangxiangbin/GitRepos/fmcs-doc/100服贸农垦/SLA/2021年/招垦资本管理（北京）有限公司_I016-2021-10-2021-12_2021年10-12月正式账单.xlsx'
    file1 = '/Users/zhangxiangbin/Desktop/SLA/深圳市招商平安资产管理有限责任公司_2023年1-6月计提账单.xlsx'
    file2 = '/Users/zhangxiangbin/Desktop/SLA/深圳市招商平安资产管理有限责任公司_2022年10-12月正式账单.xlsx'
    fileName1 = os.path.basename(file1)
    fileName2 = os.path.basename(file2)
    wb1 = openpyxl.load_workbook(file1)
    #print(wb1.sheetnames)
    wb2 = openpyxl.load_workbook(file2)
    # 比较配置：对比sheet页名称，对比key列名称，对比value列名称，标题头所在行号，数据开始行号
    #sets = [['汇总','费用类别','含税金额（元）', 8, 10],
    #        ['按量计费', '三级目录', '账单费用(含税,元)', 2, 3],
    #        ['项目包干', '项目名称', '账单费用(含税,元)', 2, 3],
    #        ['成本核算', '项目名称', '账单费用(含税,元)', 2, 3]]
    sets = [['按量计费', '三级目录', '账单费用(含税,元)', 2, 3]]
    # 2.比较账单数据,同步输出比较结果
    wb3 = openpyxl.Workbook()
    for set in sets:
        sheetName = set[0]
        sheetData1 = readSheetData(wb1[sheetName], set[1], set[2], set[3], set[4])
        print(sheetData1)
        sheetData2 = readSheetData(wb2[sheetName], set[1], set[2], set[3], set[4])
        print(sheetData2)
        diffData = compareData(sheetData1, sheetData2)
        print(diffData)
        writeSheetData(wb3, sheetName, fileName1, sheetData1, fileName2, sheetData2, diffData)
    # 删除旧的比对数据
    for filename in os.listdir():
        if filename=='SLA账单比对.xlsx':
            os.unlink(filename)
    wb3.save('SLA账单比对.xlsx')




